﻿create PROCEDURE Sync.SP_LoanInstallmentPayments_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @LoanInstallmentPayments_Temp TABLE 
		(
			LoanInstallmentPaymentID UniqueIdentifier,
			[LoanExecutionInstallmentID] uniqueidentifier,
				[PaymentAmount] float,
				[PaymentDate] datetime,
				[ReceiptID] uniqueidentifier,
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @LoanInstallmentPayments_Temp 
	   SELECT	Tbl.Col.value('LoanInstallmentPaymentID[1]','uniqueidentifier') LoanInstallmentPaymentID,
                Tbl.Col.value('LoanExecutionInstallmentID[1]', 'uniqueidentifier') [LoanExecutionInstallmentID],
Tbl.Col.value('PaymentAmount[1]', 'float') [PaymentAmount],
Tbl.Col.value('PaymentDate[1]', 'datetime') [PaymentDate],
Tbl.Col.value('ReceiptID[1]', 'uniqueidentifier') [ReceiptID],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/LoanInstallmentPayments') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[LoanInstallmentPayments] ([LoanInstallmentPaymentID],[LoanExecutionInstallmentID],[PaymentAmount],[PaymentDate],[ReceiptID])
	SELECT t.[LoanInstallmentPaymentID],t.[LoanExecutionInstallmentID],t.[PaymentAmount],t.[PaymentDate],t.[ReceiptID]
	FROM  @LoanInstallmentPayments_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.LoanInstallmentPayments_Tracking s
        WHERE t.LoanInstallmentPaymentID = s.LoanInstallmentPaymentID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[LoanInstallmentPayments]
	SET [LoanInstallmentPayments].[LoanExecutionInstallmentID] = t.[LoanExecutionInstallmentID],[LoanInstallmentPayments].[PaymentAmount] = t.[PaymentAmount],[LoanInstallmentPayments].[PaymentDate] = t.[PaymentDate],[LoanInstallmentPayments].[ReceiptID] = t.[ReceiptID] 
	FROM [dbo].[LoanInstallmentPayments] s JOIN @LoanInstallmentPayments_Temp t 
    ON t.LoanInstallmentPaymentID = s.LoanInstallmentPaymentID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.LoanInstallmentPayments_Tracking r
				  WHERE t.LoanInstallmentPaymentID = r.LoanInstallmentPaymentID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[LoanInstallmentPayments]
    FROM @LoanInstallmentPayments_Temp t JOIN [dbo].[LoanInstallmentPayments] s
    ON t.LoanInstallmentPaymentID = s.LoanInstallmentPaymentID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END











